package Dao.Impl;

import Dao.CustomerDAO;
import MyUtil.JDBCUtils;
import pojo.Customer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CustomerDAOImpl implements CustomerDAO {

    //构造方法
    public CustomerDAOImpl() throws Exception {
    }

    //用户登录
    @Override
    public int userLogin(String name, String password) throws Exception {
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        ResultSet rs;
        //查询SQL
        sql = "select * from customer where name =? and password = ?";
        ps = customerDAOImplConn.prepareStatement(sql);
        //占位符的赋值
        ps.setString(1,name);
        ps.setString(2,password);
        rs = ps.executeQuery();
        //判断结果
        if(rs.next()){
            return rs.getInt("customer_id");
        }
        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,rs);
        return -1;
    }

    //用户注册
    @Override
    public boolean userRegister(String username, String password,String registrationDate) throws Exception {
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        //增加sql语句
        sql = "insert into customer(name,password,registrationDate) values(?,?,?)";
        ps = customerDAOImplConn.prepareStatement(sql);
        ps.setString(1,username);
        ps.setString(2,password);
        ps.setString(3,registrationDate);
        int count = ps.executeUpdate();

        if (count!=0){
            return true;
        }
        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,null);
        return false;
    }

    //用户名查询
    @Override
    public boolean checkUserNameExist(String username) throws Exception {
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        ResultSet rs;
        //查询SQL
        sql = "select * from customer where name =?";
        ps = customerDAOImplConn.prepareStatement(sql);
        //占位符的赋值
        ps.setString(1,username);
        rs = ps.executeQuery();
        //判断结果
        if(rs.next()){
            return true;
        }
        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,rs);
        return false;
    }

    //根据唯一标识符检索特定客户。
    @Override
    public Customer getCustomerById(int customerId) throws Exception{
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        ResultSet rs;
        //查询SQL
        sql = "select * from customer where customer_id =?";
        ps = customerDAOImplConn.prepareStatement(sql);
        //占位符的赋值
        ps.setInt(1,customerId);
        rs = ps.executeQuery();
        //判断结果
        if(rs.next()){
            Customer customer = new Customer();
            customer.setCustomer_id(rs.getInt(1));
            customer.setName(rs.getString(2));
            customer.setContact(rs.getString(3));
            customer.setAddress(rs.getString(4));
            customer.setRegistrationDate(rs.getString(5));
            customer.setLeft_money(rs.getInt(6));
            customer.setDriver_license_number(rs.getInt(7));
            customer.setPassword(rs.getString(8));
            customer.setRent_car_number(rs.getInt(9));
            return customer;
        }
        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,rs);
        return null;
    }

    // 用户在用户界面进行修改个人信息在数据源中更新现有客户。
    @Override
    public Boolean updateCustomer(Customer customer) throws Exception {
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        System.out.println(customer);
        // 查询SQL
        sql = "UPDATE customer SET name=?, password=?, contact=?, address=?, " +
                "left_money=?, driver_license_number=?, rent_car_number=? WHERE customer_id = ?";
        // 准备SQL语句
        ps = customerDAOImplConn.prepareStatement(sql);

        // 设置参数
        ps.setString(1, customer.getName());
        ps.setString(2, customer.getPassword());
        ps.setString(3, customer.getContact());
        ps.setString(4, customer.getAddress());
        ps.setDouble(5, customer.getLeft_money());
        ps.setInt(6, customer.getDriver_license_number());
        ps.setInt(7, customer.getRent_car_number());
        ps.setInt(8, customer.getCustomer_id());

        // 执行更新
        int rowsUpdated = ps.executeUpdate();
        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,null);
        // 判断是否更新成功
        return rowsUpdated > 0;

    }

    //管理员获取所有客户信息
    @Override
    public List<Customer> getAllCustomers() throws Exception {
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        ResultSet rs;
        List<Customer> customerList = new ArrayList<>();

        sql = "select * from customer";
        ps = customerDAOImplConn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            Customer customer = new Customer();
            customer.setCustomer_id(rs.getInt("customer_id"));
            customer.setName(rs.getString("name"));
            customer.setContact(rs.getString("contact"));
            customer.setAddress(rs.getString("address"));
            customer.setRegistrationDate(rs.getString("registrationDate"));  // 数据库中存储的是日期类型
            customer.setLeft_money(rs.getInt("left_money"));
            customer.setDriver_license_number(rs.getInt("driver_license_number"));
            customer.setPassword(rs.getString("password"));  // 有密码字段
            customer.setRent_car_number(rs.getInt("rent_car_number"));
            customerList.add(customer);
        }
        return customerList;
    }

    //添加用户（管理员）
    @Override
    public Boolean addCustomer(Customer customer) throws Exception {
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        System.out.println(customer);

        sql = "insert into customer (name, contact, address, registrationDate, left_money, driver_license_number, password,rent_car_number) values (?,?, ?, ?, ?, ?, ?, ?)";
        ps = customerDAOImplConn.prepareStatement(sql);

        ps.setString(1, customer.getName());
        ps.setString(2, customer.getContact());
        ps.setString(3, customer.getAddress());
        // 对于日期，你需要将日期对象转换为正确的格式
        ps.setString(4, customer.getRegistrationDate());
        ps.setInt(5, customer.getLeft_money());
        ps.setInt(6, customer.getDriver_license_number());
        ps.setString(7, customer.getPassword());
        ps.setInt(8, customer.getRent_car_number());

        int count = ps.executeUpdate();
        if (count != 0) {
            return true;
        }
        return false;
    }

    //租车时更新个人信息，租车数量
    @Override
    public Boolean updateUserByRent(Customer customer) throws Exception {
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        System.out.println(customer);
        // 查询SQL
        sql = "UPDATE customer SET name=?, password=?, contact=?, address=?, " +
                "left_money=?, driver_license_number=?, rent_car_number=? WHERE customer_id = ?";
        // 准备SQL语句
        ps = customerDAOImplConn.prepareStatement(sql);

        // 设置参数
        ps.setString(1, customer.getName());
        ps.setString(2, customer.getPassword());
        ps.setString(3, customer.getContact());
        ps.setString(4, customer.getAddress());
        ps.setDouble(5, customer.getLeft_money());
        ps.setInt(6, customer.getDriver_license_number());
        ps.setInt(7, customer.getRent_car_number());
        ps.setInt(8, customer.getCustomer_id());

        // 执行更新
        int rowsUpdated = ps.executeUpdate();
        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,null);
        // 判断是否更新成功
        return rowsUpdated > 0;
    }

    //还车时更新个人信息，租车数量，金钱余额
    @Override
    public Boolean updateUserByReturn(Customer customer) throws Exception {
        //获取连接
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        System.out.println(customer);
        // 查询SQL
        sql = "UPDATE customer SET name=?, password=?, contact=?, address=?, " +
                "left_money=?, driver_license_number=?, rent_car_number=? WHERE customer_id = ?";
        // 准备SQL语句
        ps = customerDAOImplConn.prepareStatement(sql);

        // 设置参数
        ps.setString(1, customer.getName());
        ps.setString(2, customer.getPassword());
        ps.setString(3, customer.getContact());
        ps.setString(4, customer.getAddress());
        ps.setDouble(5, customer.getLeft_money());
        ps.setInt(6, customer.getDriver_license_number());
        ps.setInt(7, customer.getRent_car_number());
        ps.setInt(8, customer.getCustomer_id());

        // 执行更新
        int rowsUpdated = ps.executeUpdate();
        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,null);
        // 判断是否更新成功
        return rowsUpdated > 0;
    }

    //删除顾客
    @Override
    public void deletecustomer(int del_customerid) throws Exception {
        //获取连接
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        sql = "DELETE FROM customer WHERE customer_id = ?";
        ps = customerDAOImplConn.prepareStatement(sql);
        ps.setString(1, String.valueOf(del_customerid));
        ps.executeUpdate();
    }

    //根据用户名查询用户信息
    public List<Customer> geUserName(String searchText) throws Exception {

        //获取连接
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        ResultSet rs;

        List<Customer> customerList = new ArrayList<>();

        sql = "select * from customer where name like ?";
        ps = customerDAOImplConn.prepareStatement(sql);

        String fuzzyKeyword = "%"+searchText+"%";
        ps.setString(1, fuzzyKeyword);
        rs = ps.executeQuery();

        while (rs.next()){
            Customer customer = new Customer();
            customer.setCustomer_id(rs.getInt("customer_id"));
            customer.setName(rs.getString("name"));
            customer.setContact(rs.getString("contact"));
            customer.setAddress(rs.getString("address"));
            customer.setRegistrationDate(rs.getString("registrationDate"));  // 数据库中存储的是日期类型
            customer.setLeft_money(rs.getInt("left_money"));
            customer.setDriver_license_number(rs.getInt("driver_license_number"));
            customer.setPassword(rs.getString("password"));  // 有密码字段
            customer.setRent_car_number(rs.getInt("rent_car_number"));
            customerList.add(customer);
        }
        return customerList;
    }

    //管理员取消订单个人信息更新车辆数量
    @Override
    public void updateCustomerStatus(int userId) throws Exception {
        //获取连接
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        ResultSet rs;

        int carNumber=0;

        System.out.println("管理员取消订单个人信息更新车辆数量的用户Id:"+userId);
        //先查询出车辆数量，然后进行减一再存入
        String checkSql = "select rent_car_number from customer where customer_id = ?";
        ps = customerDAOImplConn.prepareStatement(checkSql);
        ps.setInt(1,userId);
        rs = ps.executeQuery();
        if (rs.next()){
            carNumber = rs.getInt("rent_car_number");
        }
        //数量减一
        carNumber = carNumber - 1;
        // 查询SQL
        sql = "UPDATE customer SET rent_car_number=? WHERE customer_id = ?";
        // 准备SQL语句
        ps = customerDAOImplConn.prepareStatement(sql);

        // 设置参数
        ps.setInt(1,carNumber);
        ps.setInt(2, userId);

        // 执行更新
        int rowsUpdated = ps.executeUpdate();
        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,rs);
    }

    @Override
    public void updatecustomerById(int data_id, Customer customer) throws Exception {
        //获取连接
        Connection customerDAOImplConn = JDBCUtils.getMyConnection();
        String sql;
        PreparedStatement ps;
        ResultSet rs;

        // 查询SQL
        sql = "UPDATE customer SET name=?, password=?, contact=?, address=?, " +
                "left_money=?, driver_license_number=?, rent_car_number=? WHERE customer_id = ?";
        // 准备SQL语句
        ps = customerDAOImplConn.prepareStatement(sql);

        // 设置参数
        ps.setString(1, customer.getName());
        ps.setString(2, customer.getPassword());
        ps.setString(3, customer.getContact());
        ps.setString(4, customer.getAddress());
        ps.setDouble(5, customer.getLeft_money());
        ps.setInt(6, customer.getDriver_license_number());
        ps.setInt(7, customer.getRent_car_number());
        ps.setInt(8, data_id);

        // 执行更新
        ps.executeUpdate();

        //关闭资源
        JDBCUtils.release(customerDAOImplConn,ps,null);
    }
}
